package com.qf.person;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author wangyu 增删改查
 * 对数据表的操作进行复用
 * @Classname: PersonDaolmpl
 * @Description: TEST
 * @date 2022-04-04 23:04
 */
public class PersonDaoimpl {
    //    新增
    public int insert(Person person) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "insert into person(name,bornDate,email,adress,age) values(?,?,?,?,?);";
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, person.getName());
            preparedStatement.setDate(2, DateUtils.utiToSql(person.getBornDate()));
            preparedStatement.setString(3, person.getEmail());
            preparedStatement.setString(4, person.getAdress());
            preparedStatement.setInt(5, person.getAge());
            int result = preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, null);
        }

        return 0;
    }

    //    修改
    public int update(Person person) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "update person set name=?,age=?,bornDate=?,email=?,adress=? where id=?";
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, person.getName());
            preparedStatement.setInt(2, person.getAge());
            preparedStatement.setDate(3, DateUtils.utiToSql(person.getBornDate()));
            preparedStatement.setString(4, person.getEmail());
            preparedStatement.setString(5, person.getAdress());
            preparedStatement.setInt(6, person.getId());
            int result = preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, null);
        }

        return 0;
    }

    //    删除
    public int delete(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "delete from person where id = ?;";
        connection = DBUtils.getConnection();

        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            int result = preparedStatement.executeUpdate();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, null);
        }

        return 0;
    }

    //    查单个
    public Person select(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from person where id =?;";
        Person person = null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                //int id, String name, int age, Date bornDate, String email, String adress
//                person = new Person();
                int pid = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                Date bornDate = resultSet.getDate("bornDate");
                String email = resultSet.getString("email");
                String adress = resultSet.getString("adress");
//                person.setId(pid);
//                person.setName(name);
//                person.setAge(age);
//                person.setBornDate(bornDate);
//                person.setEmail(email);
//                person.setAdress(adress);

                person = new Person(pid, name, age, bornDate, email, adress);
            }
            return person;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return null;
    }

    //    查所以
    public List<Person> selectAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Person person = null;
        List<Person> personList = new ArrayList<>();
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement("select * from person;");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                person = new Person();
                int pid = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                Date bornDate = resultSet.getDate("bornDate");
                String email = resultSet.getString("email");
                String adress = resultSet.getString("adress");
                person = new Person(pid, name, age, bornDate, email, adress);
                personList.add(person);
            }
            return personList;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }

        return null;
    }

    //    查单个
    public Person select(String name) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String sql = "select * from person where name =?;";
        Person person = null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                //int id, String name, int age, Date bornDate, String email, String adress
//                person = new Person();
                int pid = resultSet.getInt("id");
                String pname = resultSet.getString("name");
                int age = resultSet.getInt("age");
                Date bornDate = resultSet.getDate("bornDate");
                String email = resultSet.getString("email");
                String adress = resultSet.getString("adress");
                person = new Person(pid, pname, age, bornDate, email, adress);
            }
            return person;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, preparedStatement, resultSet);
        }
        return null;
    }

}
